package cn.edu.zjgsu.ito.service;

import java.io.IOException;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminManageScoreService {

	public static final DateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");

	public List<Record> index(){

		List<Record> allTTT = Db.find("select * from test,teacher_info,course_end "
				+ "where test.testNo=course_end.testNo and test.tNo=teacher_info.tNo "
				+ "group by test.testNo");

		List<Record> hisScore = Db.find("select * from test,course where test.courseNo=course.courseNo and testType='3'");

		for(int i = 0;i < allTTT.size();i++){
			String statusLock = "";
			String statusExport = "";
			String status = "";
			String testType = allTTT.get(i).getStr("testType");
			String uuid = allTTT.get(i).getStr("uuid");
			List<Record> testSizeNu = Db.find("select * from test where uuid=?",uuid);

			String testLock = allTTT.get(i).getStr("testLock");
			int testSize = testSizeNu.size();
			if(testType.equals("1")){
				switch(testLock){
				case "0":
					status = "正考未录入";
					statusExport = "disabled class=\"disabled\"";
					break;
				case "1":
					status = "正考已录入";
					break;
				case "2":
					status = "正考超时未录入";
					statusExport = "disabled class=\"disabled\"";
					break;
				case "3":
					status = "正考保存未提交";
					statusExport = "disabled class=\"disabled\"";
					break;
				}
				if(testSizeNu.size() > 1){
					statusLock = "disabled class=\"disabled\"";
				}
			}
			if(testType.equals("2")){
				switch(testLock){
				case "0":
					status = "补考未录入";
					statusExport = "disabled class=\"disabled\"";
					break;
				case "1":
					status = "补考已录入";
					break;
				case "2":
					status = "补考超时未录入";
					statusExport = "disabled class=\"disabled\"";
					break;
				case "3":
					status = "补考保存未提交";
					statusExport = "disabled class=\"disabled\"";
					break;
				}
				statusLock = "disabled class=\"disabled\"";
			}
			allTTT.get(i).set("status",status);
			allTTT.get(i).set("testSize",testSize);
			allTTT.get(i).set("testLock",testLock);
			allTTT.get(i).set("statusLock",statusLock);
			allTTT.get(i).set("statusExport",statusExport);

		}
		for(int i = 0;i < hisScore.size();i++){
			hisScore.get(i).set("testSize",3);
			allTTT.add(hisScore.get(i));
		}

		return allTTT;
	}

	public List<Record> stuScore(String testNo) throws ParseException{
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		List<Record> allStuGra = Db.find("select * from test,student_grade,student_info "
				+ "where test.testNo=student_grade.testNo and student_grade.passportNo=student_info.passportNo and test.testNo=?",testNo);
		for(int i=0;i<allStuGra.size();i++){
			String []b= new String[100];
			Date []date=new Date[100];
			Date dates=new Date();
			String Dates="";
			List<Record> allStuGras = Db.find("select * from newpassportno where passportNo=?",allStuGra.get(i).getStr("passportNo"));
			if(allStuGras.size()>1){
				for(int j=0;j<allStuGras.size();j++){
					b[j] = allStuGras.get(j).getStr("time");
					date[j] = formatter.parse(b[j]);
				}
				System.out.println(allStuGras.size());
				for(int a=0;a<allStuGras.size()-1;a++){
					dates = date[a];
					int c = (date[a+1]).compareTo(dates);
					if(c>0){
						dates = date[a+1];
					}
				}

				Dates = formatter.format(dates);

				Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);

				allStuGra.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}
			else{
				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",allStuGra.get(i).getStr("passportNo"));
				System.out.println(newpassportNo.getStr("newpassportNo"));
				allStuGra.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}		

		}
		return allStuGra;
	}

	public int addTest(String uuid) {

		List<Record> repecrt = Db.find("select * from test where uuid=?",uuid);
		if(repecrt.size() < 2){
			System.out.println(repecrt.get(0).getStr("testLock"));
			if(repecrt.get(0).getStr("testLock").equals("1")){
				Random ran=new Random();
				int a =ran.nextInt(99999999);
				String str = "";
				for (int i = 0; i < 10; i++) {// 你想生成几个字符的，就把4改成几，如果改成1,那就生成一个随机字母．
					str = str + (char) (Math.random() * 26 + 'a');
				}
				int b =ran.nextInt(99999999);
				String testNo = a + str + b;
				Record test = Db.findFirst("select * from test where uuid=?",uuid);
				String termValue = test.getStr("termValue");
				String courseNo = test.getStr("courseNo");
				String tNo = test.getStr("tNo");
				Record addRepTest = new Record().set("termValue",termValue).set("courseNo",courseNo).set("tNo",tNo)
						.set("uuid",uuid).set("testType",2).set("testLock",0).set("testNo",testNo);

				Record courseNa = Db.findFirst("select * from course where courseNo=?",courseNo);
				String courseName = courseNa.getStr("courseName");
				Record courseTemp = new Record().set("termValue",termValue).set("courseNo",courseNo).set("courseName",courseName)
						.set("endDate","未设置截止时间").set("testNo",testNo);
				Db.save("course_end",courseTemp);
				Db.save("test",addRepTest);

				//自动添加学生
				produceRepGra(uuid,testNo);
				return 1;
			}else{
				System.out.println("不符合添加补考。");
				return 2;
			}
		}else{
			System.out.println("已经生成了该门考试。");
			return 3;
		}
	}


	//生成补考名单
	public void produceRepGra(String uuid,String testNo){
		System.out.println("添加学生");
		List<Record> stuGraIf = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and uuid=?",uuid);

		if(stuGraIf.size() == 0){
			System.out.println("没有学生成绩，无法添加考试。");
			return;
		}

		for(int i = 0;i < stuGraIf.size();i++){
			String grade = stuGraIf.get(i).getStr("grade");
			System.out.println("====");
			if(grade.equals("A")||grade.equals("B")||grade.equals("C")||grade.equals("P")||grade.equals("F")){
				if( !grade.equals("F") ){
					stuGraIf.remove(i);
					i--;
				}
			}else{
				if(Integer.valueOf(grade) >= 60){
					stuGraIf.remove(i);
					i--;
				}
			}
		}
		if(stuGraIf.size() > 0){
			System.out.println("有不及格的人");
			//			Record testRep = Db.findFirst("select * from test where uuid=? and testType=?",uuid,2);
			//			String repTestNo= testRep.getStr("testNo");//补考考试号
			for(int i = 0;i < stuGraIf.size();i++){
				String termValue = stuGraIf.get(i).getStr("termValue");
				String majorNo = stuGraIf.get(i).getStr("majorNo");
				String majorName = stuGraIf.get(i).getStr("majorName");
				String courseNo = stuGraIf.get(i).getStr("courseNo");
				String courseName = stuGraIf.get(i).getStr("courseName");
				String passportNo = stuGraIf.get(i).getStr("passportNo");
				String tNo = stuGraIf.get(i).getStr("tNo");

				Record repStu = new Record().set("termValue",termValue).set("passportNo",passportNo).set("majorNo",majorNo)
						.set("majorName",majorName).set("courseNo",courseNo).set("courseName",courseName)
						.set("tNo",tNo).set("gradeStatus",0).set("uuid",uuid).set("testNo",testNo);
				Db.save("student_grade", repStu);
			}
		}
	}

	public List<Record> allTest() {
		List<Record> allTest = Db.find("select * from test,course where test.courseNo=course.courseNo group by test.courseNo");
		return allTest;
	}

	public void changeTimeAll(String termValue,String courseNos,String endDate) {
		List<Record> testAll = null;
		if(courseNos.equals("0")){
			testAll = Db.find("select * from test,course where test.courseNo=course.courseNo and termValue=?",termValue);
		}else{
			testAll = Db.find("select * from test,course where test.courseNo=course.courseNo and termValue=? and test.courseNo=?",termValue,courseNos);
		}

		for(int i = 0;i < testAll.size();i++){
			String testNo = testAll.get(i).getStr("testNo");
			String courseNo = testAll.get(i).getStr("courseNo");
			String courseName = testAll.get(i).getStr("courseName");
			Record is = Db.findFirst("select * from course_end where testNo=?",testNo);
			if(is == null){
				Record one = new Record().set("termValue",termValue).set("courseNo",courseNo).set("courseName",courseName)
						.set("testNo",testNo).set("endDate",endDate);
				Db.save("course_end",one);
			}else{
				Db.update("update course_end set endDate=? where courseNo=?",endDate,courseNo);
			}
		}
	}

	public void changeTime(String testNo,String changeTime) {

		Db.update("update course_end set endDate=? where testNo=?",changeTime,testNo);

	}


	public void adminLock(String testNo,String changeTime) {
		Record test = Db.findFirst("select * from test where testNo=?",testNo);
		String uuid = test.getStr("uuid");
		String testLock = test.getStr("testLock");
		String testType = test.getStr("testType");
		if(testLock.equals("0") || testLock.equals("3")){//设置时间
			changeTime(testNo, changeTime);
		}else if(testLock.equals("1") || testLock.equals("2")){//解锁，延时
			Date date = new java.util.Date();
			changeTime = formatter.format(addDateOneDay(date));
			changeTime(testNo, changeTime);
			if(testType.equals("1")){
				if(testLock.equals("1")){
					Db.update("update test set testLock='3' where testNo=?",testNo);
					//					Db.update("delete from student_grade where testNo=?",testNo);
				}else if(testLock.equals("2")){
					List<Record> student = Db.find("select * from student_grade where testNo=?",testNo);
					if(student.size() > 0){
						Db.update("update test set testLock='3' where testNo=?",testNo);
					}else{
						Db.update("update test set testLock='0' where testNo=?",testNo);
						Db.update("delete from student_grade where testNo=?",testNo);
					}
				}
			}else if(testType.equals("2")){
				//				Db.update("update test set testLock='0' where testNo=?",testNo);
				//				Db.update("delete from student_grade where testNo=?",testNo);
				//				produceRepGra(uuid,testNo);
				List<Record> student = Db.find("select * from student_grade where testNo=?",testNo);
				if(student.size() > 0){
					Db.update("update test set testLock='3' where testNo=?",testNo);
				}else{
					Db.update("update test set testLock='0' where testNo=?",testNo);
					produceRepGra(uuid,testNo);
				}
			}


		}
	}


	public static Date addDateOneDay(Date date) {  
		if (null == date) {  
			return date;  
		}  
		Calendar c = Calendar.getInstance();  
		c.setTime(date);
		c.add(Calendar.DATE, 2);
		date = c.getTime();  
		return date;  
	}

	public void exportGrade(String testNo,OutputStream out) {

		//创建一个Excel工作薄
		HSSFWorkbook book = new HSSFWorkbook();
		//在Excel工作薄中建立一张工作表
		HSSFSheet sheet = book.createSheet("学生名单");
		//设置单元格格式（文本）
		HSSFCellStyle cellStyle = book.createCellStyle();	
		//设计这些格式
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		cellStyle.setWrapText(true);//自动换行
		//设置字体
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
		cellStyle.setFont(font);

		HSSFCellStyle cellStyle1 = book.createCellStyle();
		cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		//		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//		cellStyle.setWrapText(true);//自动换行
		//设置列宽
		sheet.setColumnWidth(1,20*255);
		sheet.setColumnWidth(2,20*255);
		sheet.setColumnWidth(3,20*255);
		sheet.setColumnWidth(4,20*255);
		sheet.setColumnWidth(5,11*255);
		sheet.setColumnWidth(6,11*255);
		sheet.setColumnWidth(7,11*255);
		sheet.setColumnWidth(8,11*255);
		sheet.setColumnWidth(9,11*255);
		sheet.setColumnWidth(10,11*255);
		List<Record> student = Db.find("select * from student_grade,student_info,teacher_info "
				+ "where student_grade.passportNo=student_info.passportNo and student_grade.tNo=teacher_info.tNo and testNo=?",testNo);


		//第一行为标题
		HSSFRow row0 = sheet.createRow(0);//创建第一行
		row0.setHeight((short) (3*256));//行高
		HSSFCell cell01 = row0.createCell(0);
		HSSFCell cell02 = row0.createCell(1);
		HSSFCell cell03 = row0.createCell(3);
		HSSFCell cell04 = row0.createCell(4);
		HSSFCell cell05 = row0.createCell(6);
		HSSFCell cell06 = row0.createCell(7);
		//定义单元格字符串类型
		cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell02.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell03.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell04.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell05.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell06.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell01.setCellStyle(cellStyle);
		cell02.setCellStyle(cellStyle);
		cell03.setCellStyle(cellStyle);
		cell04.setCellStyle(cellStyle);
		cell05.setCellStyle(cellStyle);
		cell06.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell01.setCellValue("学期：");
		cell02.setCellValue(student.get(0).getStr("termValue"));
		cell03.setCellValue("课程名：");
		cell04.setCellValue(student.get(0).getStr("courseName"));
		cell05.setCellValue("教师：");
		cell06.setCellValue(student.get(0).getStr("tName"));
		//第二行 获取标题对应的数据
		HSSFRow row1 = sheet.createRow(1);//创建第二行
		row1.setHeight((short) (3*256));//行高
		HSSFCell cell11 = row1.createCell(0);
		HSSFCell cell12 = row1.createCell(1);
		HSSFCell cell13 = row1.createCell(2);
		HSSFCell cell14 = row1.createCell(3);
		HSSFCell cell15 = row1.createCell(4);
		HSSFCell cell16 = row1.createCell(5);
		HSSFCell cell17 = row1.createCell(6);
		HSSFCell cell18 = row1.createCell(7);
		HSSFCell cell19 = row1.createCell(8);
		HSSFCell cell110 = row1.createCell(9);
		HSSFCell cell111 = row1.createCell(10);
		HSSFCell cell112 = row1.createCell(11);
		//定义单元格字符串类型
		cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell16.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell17.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell18.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell19.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell110.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell111.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell112.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell11.setCellStyle(cellStyle);
		cell12.setCellStyle(cellStyle);
		cell13.setCellStyle(cellStyle);
		cell14.setCellStyle(cellStyle);
		cell15.setCellStyle(cellStyle);
		cell16.setCellStyle(cellStyle);
		cell17.setCellStyle(cellStyle);
		cell18.setCellStyle(cellStyle);
		cell19.setCellStyle(cellStyle);
		cell110.setCellStyle(cellStyle);
		cell111.setCellStyle(cellStyle);
		cell112.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell11.setCellValue("序号");
		cell12.setCellValue("护照号"); 
		cell13.setCellValue("姓");
		cell14.setCellValue("名");
		cell15.setCellValue("专业");
		cell16.setCellValue("入学年份");
		cell17.setCellValue("入学季度");
		cell18.setCellValue("平时成绩");
		cell19.setCellValue("实验成绩");
		cell110.setCellValue("其他成绩");
		cell111.setCellValue("期末成绩");
		cell112.setCellValue("总评成绩");
		//学生成绩循环输出
		for(int i = 0;i < student.size();i++){

			HSSFRow rowfor = sheet.createRow(i+2);

			String no = i+1 + "";
			String passportNo = student.get(i).getStr("passportNo");
			String lastName = student.get(i).getStr("lastName");
			String firstName = student.get(i).getStr("firstName");
			String majorName = student.get(i).getStr("majorName");
			String eduStartDate = student.get(i).getStr("eduStartDate");
			String eduSeason = student.get(i).getStr("eduSeason");
			String dailyGrade = student.get(i).getStr("dailyGrade");
			String experimentalGrade = student.get(i).getStr("experimentalGrade");
			String otherGrade = student.get(i).getStr("otherGrade");
			String finalGrade = student.get(i).getStr("finalGrade");
			String grade = student.get(i).getStr("grade");



			HSSFCell cellfor11 = rowfor.createCell(0);
			HSSFCell cellfor12 = rowfor.createCell(1);
			HSSFCell cellfor13 = rowfor.createCell(2);
			HSSFCell cellfor14 = rowfor.createCell(3);
			HSSFCell cellfor15 = rowfor.createCell(4);
			HSSFCell cellfor16 = rowfor.createCell(5);
			HSSFCell cellfor17 = rowfor.createCell(6);
			HSSFCell cellfor18 = rowfor.createCell(7);
			HSSFCell cellfor19 = rowfor.createCell(8);
			HSSFCell cellfor110 = rowfor.createCell(9);
			HSSFCell cellfor111 = rowfor.createCell(10);
			HSSFCell cellfor112 = rowfor.createCell(11);
			//定义单元格字符串类型
			cellfor11.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor12.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor13.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor14.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor15.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor16.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor17.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor18.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor19.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor110.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor111.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor112.setCellType(HSSFCell.CELL_TYPE_STRING);
			//在单元格中输入数据
			cellfor11.setCellValue(no);
			cellfor12.setCellValue(passportNo); 
			cellfor13.setCellValue(lastName);
			cellfor14.setCellValue(firstName);
			cellfor15.setCellValue(majorName);
			cellfor16.setCellValue(eduStartDate);
			cellfor17.setCellValue(eduSeason);
			cellfor18.setCellValue(dailyGrade);
			cellfor19.setCellValue(experimentalGrade);
			cellfor110.setCellValue(otherGrade);
			cellfor111.setCellValue(finalGrade);
			cellfor112.setCellValue(grade);
		}
		try {
			//写入数据 把相应的Excel工作薄存盘
			book.write(out);
			System.out.println("game over!!!");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public List<Record> tjsearch(String termValue,String courseNo,String tNo) {

		List<Record> allCourse = index();
		List<Record> searchCou = new ArrayList<Record>();
		if(!termValue.equals("0")&&!courseNo.equals("0")&&!tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if(allCourse.get(i).getStr("termValue").equals(termValue) && allCourse.get(i).getStr("courseNo").equals(courseNo)&&allCourse.get(i).getStr("tNo").equals(tNo)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(termValue.equals("0")&&!courseNo.equals("0")&&!tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if(allCourse.get(i).getStr("courseNo").equals(courseNo)&&allCourse.get(i).getStr("tNo").equals(tNo)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(!termValue.equals("0")&&courseNo.equals("0")&&!tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if(allCourse.get(i).getStr("termValue").equals(termValue)&&allCourse.get(i).getStr("tNo").equals(tNo)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(!termValue.equals("0")&&!courseNo.equals("0")&&tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if(allCourse.get(i).getStr("termValue").equals(termValue) && allCourse.get(i).getStr("courseNo").equals(courseNo)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(!termValue.equals("0")&&courseNo.equals("0")&&tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if(allCourse.get(i).getStr("termValue").equals(termValue)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(termValue.equals("0")&&!courseNo.equals("0")&&tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if( allCourse.get(i).getStr("courseNo").equals(courseNo)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(termValue.equals("0")&&courseNo.equals("0")&&!tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				if(allCourse.get(i).getStr("tNo").equals(tNo)){
					searchCou.add(allCourse.get(i));
				}
			}
		}else if(termValue.equals("0")&&courseNo.equals("0")&&tNo.equals("0")){
			for(int i = 0;i < allCourse.size();i++){
				searchCou.add(allCourse.get(i));
			}
		}
		return searchCou;

	}

	//课程超时判断
	public void timeJudge() {
		Date date = new Date();
		DateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
		List<Record> test = Db.find("select * from test,course_end where test.testNo=course_end.testNo");
		for(int i = 0;i < test.size();i++){
			String tiemtemp = test.get(i).getStr("endDate");
			String testNo = test.get(i).getStr("testNo");
			String testLock = test.get(i).getStr("testLock");
			try {

				java.util.Date courseTime = formatter.parse(tiemtemp);
				boolean temp = courseTime.before(date);
				//课程超时，testLock=2
				if(temp){
					if(testLock.equals("0") || testLock.equals("3")){
						Db.update("update test set testLock='2' where testNo=?",testNo);
					}
				}else{
					if(testLock.equals("2")){
						List<Record> students = Db.find("select * from student_grade where testNo=?",testNo);
						if(students.size() == 0){
							Db.update("update test set testLock='0' where testNo=?",testNo);
						}else{
							Db.update("update test set testLock='3' where testNo=?",testNo);
						}
					}
				}

				System.out.println(temp);
				System.out.println(testNo);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				System.out.println("考试时间未设置。");
			} 
		}
	}

	//获取所有学生个人信息
	public List<Record> getStudentInfo(){
		List<Record> stuInfo = Db.find("select * from student_info");
		return stuInfo;
	}

	public List<Record> lookStuGrades(String testNo) {
		List<Record> stuGrade = Db.find("select * from student_info,student_grade,newpassportno where student_info.passportNo=student_grade.passportNo and student_info.passportNo=newpassportno.passportNo and testNo=?",testNo);
		return stuGrade;
	}

	//管理员修改学生个人成绩
	public void updateStudentGrade(String passportNo, String testNo, String dailyGrade, String experimentalGrade, String otherGrade, String finalGrade,String grade) {
		String testType = Db.findFirst("select * from test where testNo=?",testNo).getStr("testType");
		Record stuGrade = Db.findFirst("select * from student_grade where testNo=? and passportNo=?",testNo,passportNo);
		String finalGrades = "";//最终成绩初始化
		if(testType.equals("1")){//正考
			int finalGradeTempInt = 0;//最终成绩int类型初始化
			int oneScale = Integer.valueOf(stuGrade.getStr("dailyPercent"));//拿到计算成绩比例转换成int类型
			int experimentalPercent = Integer.valueOf(stuGrade.getStr("experimentalPercent"));
			int otherPercent = Integer.valueOf(stuGrade.getStr("otherPercent"));
			int finalPercent = Integer.valueOf(stuGrade.getStr("finalPercent"));
			if(dailyGrade.equals("A") || dailyGrade.equals("B") || dailyGrade.equals("C") || dailyGrade.equals("P") || dailyGrade.equals("F")){//五级制分数计算
				String[] temp = {dailyGrade,experimentalGrade,otherGrade,finalGrade};//成绩转换成数组形式
				int[] intTemp = new int[4];//成绩数组int类型初始化
				
				for(int a = 0;a < temp.length;a++){//填充int类型数组
					switch(temp[a]){
					case "A" :
						intTemp[a] = 90;
						break;
					case "B" :
						intTemp[a] = 80;
						break;
					case "C" :
						intTemp[a] = 70;;
						break;
					case "P" :
						intTemp[a] = 60;
						break;
					case "F":
						intTemp[a] = 50;
						break;
					case "作弊":
						finalGrades = "F";
						continue;
					case "旷考":
						intTemp[a] = 0;
						break;
					default :
						intTemp[a] = 0;
						break;
					}
				}
				if(finalGrades.equals("")){
					finalGradeTempInt = ((oneScale*intTemp[0] + experimentalPercent*intTemp[1] + otherPercent*intTemp[2] + finalPercent*intTemp[3])/100);
				}
			}else{//百分制
				int oneGrade = Integer.valueOf(dailyGrade);
				int twoGrade = Integer.valueOf(experimentalGrade);
				int thrGrade = Integer.valueOf(otherGrade);
				int fouGrade = Integer.valueOf(finalGrade);
				finalGradeTempInt = ((oneScale*oneGrade + experimentalPercent*twoGrade + otherPercent*thrGrade + finalPercent*fouGrade)/100);
			}
			if(finalGradeTempInt >= 90){
				finalGrades = "A";
			}else if(finalGradeTempInt >= 80){
				finalGrades = "B";
			}else if(finalGradeTempInt >= 70){
				finalGrades = "C";
			}else if(finalGradeTempInt >= 60){
				finalGrades = "P";
			}else if(finalGradeTempInt < 60){
				finalGrades = "F";
			}
			//成绩更新数据库
			Db.update("update student_grade set dailyGrade=?,experimentalGrade=?,otherGrade=?,finalGrade=?,grade=? where testNo=? and passportNo=?",dailyGrade,experimentalGrade,otherGrade,finalGrade,finalGrades,testNo,passportNo);
		}else if(testType.equals("2")){//补考
			if(!(grade.equals("A") || grade.equals("B") || grade.equals("C") || grade.equals("P") || grade.equals("F") || grade.equals("旷考") || grade.equals("作弊"))){
				int gradeInt = Integer.valueOf(grade);
				if(gradeInt >= 90){
					finalGrades = "A";
				}else if(gradeInt >= 80){
					finalGrades = "B";
				}else if(gradeInt >= 70){
					finalGrades = "C";
				}else if(gradeInt >= 60){
					finalGrades = "P";
				}else if(gradeInt < 60){
					finalGrades = "F";
				}
			}else{
				finalGrades = grade;
			}
			Db.update("update student_grade set grade=? where testNo=? and passportNo=?",finalGrades,testNo,passportNo);
		}
	}

	//查看成绩制度
	public List<Record> lookSystem(String uuid) {
		List<Record> stuGrade = Db.find("select * from student_grade where uuid=?",uuid);
		return stuGrade;
	}


}
